package Or

import (
	"context"
	"fmt"
	"db2s/ETL"
	"db2s/global"
	"db2s/parDef"
	"db2s/ref"
	"math"
	"strconv"
	"strings"
)

type constraintTypeMeS struct {
	NameB       string `json:"name"`
	ConstraintB string `json:"constraint"`
}
type indexColumnMeS struct {
	SchemaName string `json:"databaseName"`
	TableName  string `json:"tableName"`
	IndexName  string `json:"indexName"`
	ColumnName string `json:"columnName"`
	ColumnSeq  string `json:"columnSeq"`
}
type indexCategoryMe struct {
	SchemaName    string `json:"databaseName"`
	TableName     string `json:"tableName"`
	IndexName     string `json:"indexName"`
	NonUnique     string `json:"nonUnique"`
	Cardinality   int64  `json:"cardinality"`
	IndexCategory string `json:"indexCategory"`
}

type indexColumnDistinctMeS struct {
	ColumnName     string `json:"columnName"`
	ColumnDistinct string `json:"columnDistinct"`
}

var intType = map[string]string{"NUMBER": "int", "INTEGER": "int", "FLOAT": "int", "REAL": "int", "DOUBLE": "int", "DECIMAL": "int", "BINARY_FLOAT": "int", "BINARY_DOUBLE": "int"}
var timeType = map[string]string{"DATE": "time", "TIMESTAMP": "time", "INTERVAL YEAR": "time", "INTERVAL DAY": "time"}
var stringType = map[string]string{"CHAR": "string", "VARCHAR2": "string", "NCHAR": "string", "NVARCHAR2": "string"}
var typeCost = map[string]int{"int": 1, "time": 2, "string": 3, "text": 4}

func (im IndexMe) constraintType(ctx context.Context) (result global.Return, err error) {
	var event = "[constraintType]"
	defer func() {
		if r := ref.RecoverPanic(event, recover()); err == nil && r != nil {
			err = r
		}
	}()
	select {
	case <-ctx.Done():
		err = ctx.Err()
		return
	default:
		if result, err = parDef.SqlExec(parDef.GetSubSqlExecFuncParameterValue(ctx, event, ETL.SMap, tableConstraintsSysObjectView, getTableIndexMetaSql, QueryColValManage)); err != nil {
			err = ref.ErrAddPrintf(event, err)
			return
		}
		if result.Result == nil {
			return
		}
		var p = make(map[string]string)
		var res []constraintTypeMeS
		for _, v := range result.Result.([]map[string]any) {
			vv := mapAnyToString(v)
			name, ok1 := vv["name"]
			constraint, ok2 := vv["constraint"]
			if ok1 && ok2 {
				p[name] = constraint
			}
			var dd = constraintTypeMeS{}
			if err = mapStringToStruct(vv, &dd); err != nil {
				err = ref.ErrAddPrintf("constraintType", err)
				return
			}
			res = append(res, dd)
		}
		result.Result, result.InsideResult = p, res
		return
	}
}
func (im IndexMe) indexColumn(ctx context.Context) (result global.Return, err error) {
	var event = "[indexColumn]"
	defer func() {
		if r := ref.RecoverPanic(event, recover()); err == nil && r != nil {
			err = r
		}
	}()
	select {
	case <-ctx.Done():
		err = ctx.Err()
		return
	default:
		if result, err = parDef.SqlExec(parDef.GetSubSqlExecFuncParameterValue(ctx, event, ETL.SMap, indexColumnSysObjectView, getIndexColumnMetaSql, QueryColValManage)); err != nil {
			err = ref.ErrAddPrintf(event, err)
			return
		}
		if result.Result == nil {
			return
		}
		var res []indexColumnMeS
		var p []map[string]string
		for _, v := range result.Result.([]map[string]interface{}) {
			vv := mapAnyToString(v)
			p = append(p, vv)
			var dd = indexColumnMeS{}
			if err = mapStringToStruct(vv, &dd); err != nil {
				err = ref.ErrAddPrintf("indexColumn", err)
				return
			}
			res = append(res, dd)
		}
		result.Result, result.InsideResult = p, res
		return
	}
}
func (im IndexMe) indexCategory(ctx context.Context) (result global.Return, err error) {
	var event = "[indexCategory]"
	defer func() {
		if r := ref.RecoverPanic(event, recover()); err == nil && r != nil {
			err = r
		}
	}()
	select {
	case <-ctx.Done():
		err = ctx.Err()
		return
	default:
		if result, err = parDef.SqlExec(parDef.GetSubSqlExecFuncParameterValue(ctx, event, ETL.SMap, indexCategorySysObjectView, getIndexCategoryMetaSql, QueryColValManage)); err != nil {
			err = ref.ErrAddPrintf(event, err)
			return
		}
		if result.Result == nil {
			return
		}
		var res []indexCategoryMe
		var p []map[string]any
		for _, v := range result.Result.([]map[string]any) {
			vv := mapNullActive(v)
			if vvp, ok := vv["indexCategory"]; ok {
				switch vvp {
				case "NO":
					vv["indexCategory"] = "global"
				case "YES":
					vv["indexCategory"] = "local"
				}
			}
			p = append(p, vv)
			var dd = indexCategoryMe{}
			if err = ref.MapToStruct(vv, &dd); err != nil {
				err = ref.ErrAddPrintf("indexCategory", err)
				return
			}
			res = append(res, dd)
		}
		result.Result, result.InsideResult = p, res
	}
	return
}
func (im IndexMe) nameColumnBase(ctx context.Context) (colMeta, constraintMeta, columnMeta, categoryMeta global.Return, err error) {
	cm := ColumnMe{}
	if colMeta, err = cm.Meta(ctx); err != nil {
		err = ref.ErrAddPrintf("nameColumnBase", err)
		return
	}
	if colMeta, err = cm.MetaMap(parDef.SwapCtxKeyValue(ctx,"meta",colMeta.Result)); err != nil {
		err = ref.ErrAddPrintf("nameColumnBase", err)
		return
	}
	if constraintMeta, err = im.constraintType(ctx); err != nil {
		err = ref.ErrAddPrintf("nameColumnBase", err)
		return
	}
	if columnMeta, err = im.indexColumn(ctx); err != nil {
		err = ref.ErrAddPrintf("nameColumnBase", err)
		return
	}
	if categoryMeta, err = im.indexCategory(ctx); err != nil {
		err = ref.ErrAddPrintf("nameColumnBase", err)
		return
	}
	return
}
func (im IndexMe) constraintBase(constraintMeta global.Return, indexName string) (constraintType string) {
	if constraintMeta.Result == nil {
		return
	}
	for _, cons := range constraintMeta.InsideResult.([]constraintTypeMeS) {
		if indexName == cons.NameB {
			constraintType = cons.ConstraintB
			return
		}
	}
	return
}
func (im IndexMe) indexColumnBase(colMeta, indexColumnMeta global.Return, indexName string) (res []parDef.IdxSubColumnMe) {
	if indexColumnMeta.Result == nil {
		return
	}
	for _, column := range indexColumnMeta.InsideResult.([]indexColumnMeS) {
		if indexName == column.IndexName {
			var dd = parDef.IdxSubColumnMe{ColumnName: column.ColumnName}
			for _, columnInfo := range colMeta.Result.([]parDef.ColMetaMapS) {
				if columnInfo.ColumnName == column.ColumnName {
					dd.IndexSeq = fmt.Sprintf("%v", column.ColumnSeq)
					dd.ColumnSeq = fmt.Sprintf("%v", columnInfo.ColumnSeq)
					dd.ColumnNull = columnInfo.ColNullMap
					dd.ColumnType = columnInfo.Type.ColTypeMap
					dd.Collation = columnInfo.CollationName
					res = append(res, dd)
					break
				}
			}
		}
	}
	return
}

func (im IndexMe) newColumnInit(allIndex global.Return) (res []parDef.IndexColumnMe) {
	if allIndex.Result == nil {
		return
	}
	for _, v := range allIndex.InsideResult.([]indexCategoryMe) {
		res = append(res, parDef.IndexColumnMe{
			Schema:        v.SchemaName,
			Table:         v.TableName,
			IndexName:     v.IndexName,
			IndexCategory: v.IndexCategory,
			NonUnique:     v.NonUnique,
			Cardinality:   v.Cardinality,
		})
	}
	return
}
func (im IndexMe) columnKey(vv parDef.IndexColumnMe, constraintMeta global.Return) (result parDef.IndexColumnMe) {
	result = vv
	switch strings.TrimSpace(im.constraintBase(constraintMeta, vv.IndexName)) {
	case "P":
		result.ColumnKey = "P"
		result.IndexName = "PRIMARY"
	case "U":
		result.ColumnKey = "U"
	default:
		if strings.EqualFold(vv.NonUnique, "UNIQUE") {
			result.ColumnKey = "U"
		} else {
			result.ColumnKey = "I"
		}
	}
	return
}

func (im IndexMe) columnDistinct(ctx context.Context) (result global.Return, err error) {
	var event = "[columnDistinct]"
	defer func() {
		if r := ref.RecoverPanic(event, recover()); err == nil && r != nil {
			err = r
		}
	}()
	select {
	case <-ctx.Done():
		err = ctx.Err()
		return
	default:
		if result, err = parDef.SqlExec(parDef.GetSubSqlExecFuncParameterValue(ctx, event, ETL.SMap, columnDistinctSysObjectView, getColumnDistinctMetaSql, QueryColValManage)); err != nil {
			err = ref.ErrAddPrintf(event, err)
			return
		}
		if result.Result == nil {
			return
		}
		var res []indexColumnDistinctMeS
		var p = make(map[string]any)
		for _, v := range result.Result.([]map[string]any) {
			vv := mapNullActive(v)
			columnName, ok1 := vv["columnName"]
			columnDistinct, ok2 := vv["columnDistinct"]
			if ok1 && ok2 {
				p[fmt.Sprintf("%v", columnName)] = columnDistinct
			}
			var dd = indexColumnDistinctMeS{}
			if err = ref.MapToStruct(vv, &dd); err != nil {
				err = ref.ErrAddPrintf("columnDistinct", err)
				return
			}
			res = append(res, dd)
		}
		result.Result, result.InsideResult = p, res
		return
	}
}
func (im IndexMe) NameColumn(ctx context.Context) (result global.Return, err error) {
	var colMeta, constraintMeta, indexColumnMeta, allIndex global.Return
	if colMeta, constraintMeta, indexColumnMeta, allIndex, err = im.nameColumnBase(ctx); err != nil {
		err = ref.ErrAddPrintf("NameColumn", err)
		return
	}
	res := im.newColumnInit(allIndex)
	var resStr []parDef.IndexColumnMe
	for _, vv := range res {
		var newNameColumnMe = vv
		vv.ColumnMate = im.indexColumnBase(colMeta, indexColumnMeta, vv.IndexName)
		if len(vv.ColumnMate) == 0 {
			continue
		}
		newNameColumnMe = im.columnKey(vv, constraintMeta)
		resStr = append(resStr, newNameColumnMe)
	}
	result.Result = resStr
	return
}

// 按照索引离散度倒序排序（离散度最多两个列）
func (im IndexMe) cardinalityReverseSort(IIData []parDef.IndexColumnMe, Sum int64) (result []string) {
	var cardinality = make(map[string]int64)
	for _, v := range IIData {
		if Sum > 0 && v.Cardinality > 0 && v.Cardinality*100/Sum > 30 {
			cardinality[v.IndexName] = v.Cardinality
		}
	}
	result = ref.MapValueReverseSort(cardinality)
	return
}
func specifyIndexNameToNameColumnMe(specifyIndex string, IIData []parDef.IndexColumnMe) (p parDef.IndexColumnMe) {
	for _, vv := range IIData {
		if strings.EqualFold(specifyIndex, vv.IndexName) {
			p = vv
			return
		}
	}
	return
}
func compoundIndexColumnHistogram(columnHis []parDef.IdxSubColumnMe, cDistinctMeta any) (hist []int64) {
	var err error
	for k, vv := range columnHis {
		if k > 1 {
			continue
		}
		if colHist, ok := cDistinctMeta.(map[string]any)[vv.ColumnName]; ok {
			var hv int64
			if hv, err = strconv.ParseInt(strings.TrimSpace(fmt.Sprintf("%v", colHist)), 10, 64); err != nil {
				hist = append(hist, 0)
			} else {
				hist = append(hist, hv)
			}
		}
	}
	return
}
func (im IndexMe) columnCardinalityStandard(hist []int64, Sum int64) bool {
	for _, vv := range hist {
		if vv*100/Sum > 30 {
			return true
		}
	}
	return false
}

// 比较单列和两个列的直方图，选择合适的
func (im IndexMe) columnHistogram(ctx context.Context, IIData []parDef.IndexColumnMe, Sum int64) (result []string, err error) {
	var columnDistinctMeta global.Return
	if Sum == 0 {
		return
	}
	if columnDistinctMeta, err = im.columnDistinct(ctx); err != nil {
		err = ref.ErrAddPrintf("columnHistogram", err)
		return
	}
	if columnDistinctMeta.Result == nil {
		return
	}
	var cost = make(map[string]any)
	for _, v := range IIData {
		hist := compoundIndexColumnHistogram(v.ColumnMate, columnDistinctMeta.Result)
		switch {
		case len(hist) == 1 && hist[0] > 0 && im.columnCardinalityStandard(hist, Sum):
			cost[v.IndexName] = math.Round((float64(Sum)/float64(hist[0]))*100) / 100
		case len(hist) > 1 && hist[0] > 0 && hist[1] > 0 && im.columnCardinalityStandard(hist, Sum):
			var re, factor float64 = 0, 1
			for _, h := range hist {
				re = factor * math.Round((float64(Sum)/float64(h))*100) / 100
				factor = re
			}
			cost[v.IndexName] = re
		}
	}
	result = ref.MapValueSort(cost)
	return
}

func typeSelection(c parDef.IdxSubColumnMe) (cost int) {
	for _, v := range []map[string]string{intType, timeType, stringType} {
		if t, ok := v[strings.ToUpper(strings.Split(c.ColumnType, "(")[0])]; ok {
			return typeCost[t]
		}
	}
	return 100
}
func specifyIndexColumnTypeCost(IIData []parDef.IdxSubColumnMe) (cost int) {
	for _, v := range IIData {
		cost += typeSelection(v)
	}
	return
}

func (im IndexMe) typeScreening(screeningIndex []string, IIData []parDef.IndexColumnMe) (result string) {
	var indexTypeCost = make(map[string]any)
	for _, v := range screeningIndex {
		p := specifyIndexNameToNameColumnMe(v, IIData)
		indexTypeCost[v] = specifyIndexColumnTypeCost(p.ColumnMate)
	}
	res := ref.MapValueSort(indexTypeCost)
	if len(res) >= 1 {
		result = res[0]
	}
	return
}
func (im IndexMe) KeyChoose(ctx context.Context) (result global.Return, err error) {
	var res, indexColumnQueryData global.Return
	var Sum int64
	var event = "[KeyChoose]"
	c := SumF{}
	newCtx := parDef.SwapCtxKeyValue(ctx, "expect", ETL.String)
	if res, err = c.Rows(newCtx); err != nil {
		err = ref.ErrAddPrintf(event, err)
		return
	}
	if res.Result == nil {
		return
	}
	if Sum, err = ref.StringAnyToInt64(res.Result); err != nil {
		err = ref.ErrAddPrintf(event, err)
	}
	if indexColumnQueryData, err = im.NameColumn(ctx); err != nil {
		err = ref.ErrAddPrintf("KeyChoose", err)
		return
	}
	if indexColumnQueryData.Result == nil {
		return
	}
	var IIData = indexColumnQueryData.Result.([]parDef.IndexColumnMe)
	var screeningIndex []string
	if screeningIndex, err = im.columnHistogram(ctx, IIData, Sum); err != nil {
		err = ref.ErrAddPrintf("KeyChoose", err)
		return
	}
	if len(screeningIndex) == 0 {
		//screeningIndex = im.cardinalityReverseSort(IIData, Sum)
		return
	}
	chooseIndex := im.typeScreening(screeningIndex, IIData)
	for _, v := range IIData {
		if strings.EqualFold(v.IndexName, chooseIndex) {
			result.Result = v
			return
		}
	}
	return
}

func (im IndexMe) NameToColumnClip(ctx context.Context) (result global.Return, err error) {
	var metaBase MetaBaseInitResult
	var res = make(map[string][]string)
	if metaBase, err = metaBaseInit(ctx); err != nil {
		err = ref.ErrAddPrintf("NameToColumnClip", err)
		return
	}
	for _, indexCol := range metaBase.indexMeta {
		res[strings.ToUpper(fmt.Sprintf("%v@%v", indexCol.IndexName, indexCol.ColumnKey))] = func() (res []string) {
			for _, p := range indexCol.ColumnMate {
				res = append(res, strings.ToUpper(p.ColumnName))
			}
			return
		}()
	}
	result.Result = res
	return
}
